﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace 旅游管理员终端
{
    public partial class DataOperation : Form
    {
        public MySqlConnection conn;
        private String connectStr;
        MySqlDataAdapter mySqlDataAdapter;
        DataSet dataSet;
        String sqlstr;
        //预加载下拉框文件
        string[] optionSelect = System.IO.File.ReadAllLines("option.txt");
        string[] sqlPart = System.IO.File.ReadAllLines("sql.txt");

        public DataOperation(string str)
        {
            InitializeComponent();
            connectStr = str;
        }
        
        public void Database_connection()
        {
            //打开数据库连接
            try
            {
                conn = new MySqlConnection(connectStr);
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }

        private void DataSearch_Load(object sender, EventArgs e)
        {
            //页面加载函数，打开页面即执行数据库连接
            Database_connection();
        }

        public void Database_Select_Option(MySqlConnection conn,String sqlstr)
        {
            //进行查询选择
            if (nameTextBox.Text==null&&levelTextBox.Text==null&&scoreTextBox.Text==null&&locationTextBox.Text==null&&addressTextBox.Text==null&&maxhotTextBox.Text==null&&minhotTextBox.Text==null&&rankTextBox.Text==null)
            {
                //默认全控为查找全部
                sqlstr = "select * from attra_tbl";
            }
            else
            {
                sqlstr = "select * from attra_tbl where ";
                if (nameTextBox.Text != "") sqlstr = sqlstr + "name like '%" + nameTextBox.Text + "%' and ";
                if (levelTextBox.Text != "") sqlstr = sqlstr + "lev = '" + levelTextBox.Text + "' and ";
                if (scoreTextBox.Text != "") sqlstr = sqlstr + "score = '" + scoreTextBox.Text + "' and ";
                if (locationTextBox.Text != "") sqlstr = sqlstr + "loc like '%" + locationTextBox.Text + "%' and ";
                if (addressTextBox.Text != "") sqlstr = sqlstr + "address like '%" + addressTextBox.Text + "%' and ";
                if (maxhotTextBox.Text != "") sqlstr = sqlstr + "hot <= '" + maxhotTextBox.Text + "' and ";
                if (minhotTextBox.Text != "") sqlstr = sqlstr + "hot >= '" + minhotTextBox.Text + "' and ";
                if (rankTextBox.Text != "") sqlstr = sqlstr + "city_sort like '%" + rankTextBox.Text + "%' and ";
                sqlstr = sqlstr.Substring(0, sqlstr.Length - 4);
            }
            //向DataGridView中注入数据    
            MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
            mySqlDataAdapter = new MySqlDataAdapter(cmd);
            dataSet = new DataSet();
            mySqlDataAdapter.Fill(dataSet, "attra_tbl");
            dataGridView.DataSource = dataSet;
            dataGridView.DataMember = "attra_tbl";
            //由于mediumblob数据无法在视图中显示出来，因此为防止致命错误将其隐藏
            dataGridView.Columns[10].Visible = false;
        }

        private void Clear_Click(object sender, EventArgs e)
        {
            //清空视图，用于重新获取信息
            dataGridView.Columns.Clear();
        }

        private void Query_Click(object sender, EventArgs e)
        {
            try
            {
                //查找按钮，执行查找函数
                Database_Select_Option(conn, sqlstr);
            }
            catch(Exception ex)
            {
                Hint h = new Hint(ex);
                h.ShowDialog();
            } 
        }

        private void Exit_Click(object sender, EventArgs e)
        {
            //退出页面
            this.Close();
        }

        private void save_and_submit_button_Click(object sender, EventArgs e)
        {
            //保存并提交，此按钮用于修改，增添和删除数据
            MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(mySqlDataAdapter);
            mySqlDataAdapter.Update(dataSet, "attra_tbl");
        }

        private void refreshButton_Click(object sender, EventArgs e)
        {
            dataSet.Tables["attra_tbl"].Clear();
            mySqlDataAdapter.Fill(dataSet, "attra_tbl");
        }
    }
}
